package com.feonix.common.util.jdbc;

import java.util.HashMap;
import java.util.LinkedList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;

import com.feonix.common.annotation.ColumnType;
import com.feonix.common.basedao.EntityProperty;
import com.feonix.common.util.StringUtil;

public class SQLServerDialect extends AbsDialect implements Dialect {

	private static final Pattern ALIAS_PATTERN = Pattern.compile("(?i)\\s(as)?\\s(.)+$");

	private static final Pattern PARAMSPATTERN = Pattern.compile(":\\w*", 2);
	private boolean topAdded;
	private boolean hasOffset = true;

	public boolean supportsLimit() {
		return true;
	}

	public boolean useMaxForLimit() {
		return true;
	}

	public boolean supportsLimitOffset() {
		return true;
	}

	public boolean supportsVariableLimit() {
		return true;
	}

	public int convertToFirstRowValue(int paramInt) {
		return (paramInt + 1);
	}

	public String getLimitString(String sql, int offset, int limit) {
		Matcher matcher = PARAMSPATTERN.matcher(sql);
		StringBuffer stringBuffer = new StringBuffer();
		HashMap<String, String> hashMap = new HashMap<String, String>();
		int i = 0;
		while (matcher.find()) {
			hashMap.put("{" + i + "}", matcher.group());
			matcher.appendReplacement(stringBuffer, "{" + i + "}");
			++i;
		}
		matcher.appendTail(stringBuffer);
		StringBuilder stringBuilder = new StringBuilder(stringBuffer.toString().toLowerCase());

		if (stringBuilder.charAt(stringBuilder.length() - 1) == ';') {
			stringBuilder.setLength(stringBuilder.length() - 1);
		}

		int j = shallowIndexOfWord(stringBuilder, "order by", 0);
		if (j > 0) {
			addTopExpression(stringBuilder, limit + offset);
		}

		encloseWithOuterQuery(stringBuilder);

		stringBuilder.insert(0, "WITH query AS (").append(") SELECT ").append("*").append(" FROM query ");

		stringBuilder.append("WHERE __hibernate_row_nr__ >= ").append(offset + 1)
				.append(" AND __hibernate_row_nr__ <= ").append(limit + offset);

		sql = StringUtils.replaceEach(stringBuilder.toString(), hashMap.keySet().toArray(new String[0]),
				hashMap.values().toArray(new String[0]));
		return sql;
	}

	protected String fillAliasInSelectClause(StringBuilder sb) {
		LinkedList<String> linkedList = new LinkedList<String>();
		int i = shallowIndexOf(sb, "select ", 0);
		int j = shallowIndexOfWord(sb, "from", i);
		int k = i;
		int l = i;
		int i1 = 0;
		int i2 = 0;
		String str2;
		while (k != -1) {
			l = k;
			k = shallowIndexOf(sb, ",", k);
			if (k > j) {
				break;
			}
			if (k != -1) {
				String str1 = sb.substring(l, k);
				if (selectsMultipleColumns(str1)) {
					i2 = 1;
				} else {
					str2 = getAlias(str1);
					if (str2 == null) {
						str2 = StringHelper.generateAlias("page", i1);
						sb.insert(k, " as " + str2);
						int i3 = " as ".length() + str2.length();
						++i1;
						k += i3;
						j += i3;
					}
					linkedList.add(str2);
				}
				++k;
			}

		}

		j = shallowIndexOfWord(sb, "from", i);
		String str1 = sb.substring(l, j);
		if (selectsMultipleColumns(str1)) {
			i2 = 1;
		} else {
			str2 = getAlias(str1);
			if (str2 == null) {
				str2 = StringHelper.generateAlias("page", i1);
				sb.insert(j - 1, " as " + str2);
			}
			linkedList.add(str2);
		}

		return ((i2 != 0) ? "*" : StringHelper.join(", ", linkedList.iterator()));
	}

	private boolean selectsMultipleColumns(String expression) {
		String str = expression.trim().replaceFirst("(?i)(.)*\\s", "");
		return (("*".equals(str)) || (str.endsWith(".*")));
	}

	private String getAlias(String expression) {
		Matcher matcher = ALIAS_PATTERN.matcher(expression);
		if (matcher.find()) {
			return matcher.group(0).replaceFirst("(?i)(.)*\\sas\\s", "").trim();
		}
		return null;
	}

	protected void encloseWithOuterQuery(StringBuilder sql) {
		sql.insert(0,
				"SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM ( ");
		sql.append(" ) inner_query ");
	}

	protected void addTopExpression(StringBuilder sql, int max) {
		int i = shallowIndexOfWord(sql, "distinct", 0);
		if (i > 0) {
			sql.insert(i + "distinct".length(), " TOP(" + max + ")");
		} else {
			int j = shallowIndexOf(sql, "select ", 0);

			sql.insert(j + "select".length(), " TOP(" + max + ")");
		}
		this.topAdded = true;
	}

	private static int shallowIndexOfWord(StringBuilder sb, String search, int fromIndex) {
		int i = shallowIndexOf(sb, ' ' + search + ' ', fromIndex);

		return ((i != -1) ? i + 1 : -1);
	}

	private static int shallowIndexOf(StringBuilder stringBuilder, String paramString, int paramInt) {
		String str = stringBuilder.toString().toLowerCase();
		int i = str.length();
		int j = paramString.length();
		int k = -1;
		int l = 0;
		int i1 = paramInt;
		do {
			k = str.indexOf(paramString, i1);
			if (k != -1) {
				for (int i2 = i1; i2 < k; ++i2) {
					int i3 = stringBuilder.charAt(i2);
					if (i3 == 40)
						++l;
					else if (i3 == 41) {
						--l;
					}
				}
				i1 = k + j;
			}
		} while ((i1 < i) && (l != 0) && (k != -1));
		return ((l == 0) ? k : -1);
	}

	public String getDateTimeColumn(EntityProperty paramEntityProperty) {
		String str = null;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(paramEntityProperty.getColumnName()).append(" DATETIME");
		setConstraint(paramEntityProperty, stringBuffer);
		str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String getDateColumn(EntityProperty paramEntityProperty) {
		String str = null;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(paramEntityProperty.getColumnName()).append(" DATETIME");
		setConstraint(paramEntityProperty, stringBuffer);
		str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String getIntColumn(EntityProperty paramEntityProperty) {
		String str = null;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(paramEntityProperty.getColumnName()).append(" INT");
		if (StringUtil.isNotEmpty(paramEntityProperty.getDefaultValue())) {
			stringBuffer.append(" DEFAULT " + paramEntityProperty.getDefaultValue());
		}
		setConstraint(paramEntityProperty, stringBuffer);
		str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String getDoubleColumn(EntityProperty paramEntityProperty) {
		String str = null;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(paramEntityProperty.getColumnName()).append(" DECIMAL(")
				.append(paramEntityProperty.getLength()).append(",").append(paramEntityProperty.getDecimalLenght())
				.append(")");
		if (StringUtil.isNotEmpty(paramEntityProperty.getDefaultValue())) {
			stringBuffer.append(" DEFAULT " + paramEntityProperty.getDefaultValue());
		}
		setConstraint(paramEntityProperty, stringBuffer);
		str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String getFloatColumn(EntityProperty paramEntityProperty) {
		String str = null;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(paramEntityProperty.getColumnName()).append(" DECIMAL(")
				.append(paramEntityProperty.getLength()).append(",").append(paramEntityProperty.getDecimalLenght())
				.append(")");
		if (StringUtil.isNotEmpty(paramEntityProperty.getDefaultValue())) {
			stringBuffer.append(" DEFAULT " + paramEntityProperty.getDefaultValue());
		}
		setConstraint(paramEntityProperty, stringBuffer);
		str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String getVarcharColumn(EntityProperty paramEntityProperty) {
		String str = null;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(paramEntityProperty.getColumnName()).append(" VARCHAR(")
				.append(paramEntityProperty.getLength()).append(")");
		if (StringUtil.isNotEmpty(paramEntityProperty.getDefaultValue())) {
			stringBuffer.append(" DEFAULT '" + paramEntityProperty.getDefaultValue() + "'");
		}
		setConstraint(paramEntityProperty, stringBuffer);
		str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String getCharColumn(EntityProperty paramEntityProperty) {
		String str = null;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(paramEntityProperty.getColumnName()).append(" char(")
				.append(paramEntityProperty.getLength()).append(")");
		if (StringUtil.isNotEmpty(paramEntityProperty.getDefaultValue())) {
			stringBuffer.append(" DEFAULT '" + paramEntityProperty.getDefaultValue() + "'");
		}
		setConstraint(paramEntityProperty, stringBuffer);
		str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String getTextColumn(EntityProperty paramEntityProperty) {
		String str = null;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(paramEntityProperty.getColumnName()).append(" text");
		if (StringUtil.isNotEmpty(paramEntityProperty.getDefaultValue())) {
			stringBuffer.append(" DEFAULT '" + paramEntityProperty.getDefaultValue() + "'");
		}
		setConstraint(paramEntityProperty, stringBuffer);
		str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String getBlobColumn(EntityProperty paramEntityProperty) {
		String str = null;
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(paramEntityProperty.getColumnName()).append(" image");

		setConstraint(paramEntityProperty, stringBuffer);
		str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String getGeneratorColumn(EntityProperty paramEntityProperty) {
		StringBuffer stringBuffer = new StringBuffer();
		switch (paramEntityProperty.getGeneratorType().ordinal()) {
		case 1:
			stringBuffer.append(" IDENTITY (1, 1)");
		}

		if (paramEntityProperty.isPk()) {
			stringBuffer.append(" PRIMARY KEY");
		}
		String str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String getCharset() {
		return "";
	}

	public String addColumn(String paramString1, String paramString2, ColumnType paramColumnType, String paramString3,
			String paramString4) {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("ALTER TABLE ").append(paramString1).append(" ADD ").append(paramString2).append(" ")
				.append(a(paramColumnType));
		if ((paramColumnType != ColumnType.INT) && (StringUtil.isNotEmpty(paramString3))) {
			stringBuffer.append("(").append(paramString3).append(")");
		}
		if (StringUtil.isNotEmpty(paramString4)) {
			stringBuffer.append(" DEFAULT ").append(paramString4);
		}
		String str = stringBuffer.toString();
		StringUtil.cleanStringBuffer(new StringBuffer[] { stringBuffer });
		return str;
	}

	public String updateColumn(String paramString1, String paramString2, EntityProperty paramEntityProperty) {
		return null;
	}

	private String a(ColumnType paramColumnType) {
		String str = null;
		switch (paramColumnType.ordinal() + 1) {
		case 9:
			str = "image";
			break;
		case 7:
			str = "char";
			break;
		case 1:
			str = "DATETIME";
			break;
		case 2:
			str = "DATETIME";
			break;
		case 4:
			str = "DECIMAL";
			break;
		case 5:
			str = "DECIMAL";
			break;
		case 3:
			str = "INT";
			break;
		case 8:
			str = "text";
			break;
		case 10:
			str = "text";
			break;
		case 6:
			str = "VARCHAR";
		}

		return str;
	}
}
